﻿using System;
using System.Collections.Generic;
using System.Data;
using SdmxMl.Common;
using SdmxMl.Structure;
using System.Windows.Forms;
using SdmxMl.Manager;
using SdmxMl.Helper;

namespace SdmxMl.SmsImportCfg
{
    /// <summary>
    /// Helper for importing Codelists, Categories and Concepts
    /// </summary>
    public class ImportArtefactExcel
    {
        public const string TYPE_VALID_FROM = "VALID_FROM";
        public const string TYPE_VALID_TO = "VALID_TO";
        private const string DATE_FORMAT = "yyyy-MM-dd";

        #region Internal Members

        private ArtefactCfg _arCfg;
        private BaseCfg _cfg;
        private DataTable _dtWorksheet;
        WorkSheetHelper wh;

        private bool cleanEmptyAnnotations;

        #endregion //Internal Members

        #region Construction

        /// <summary>
        /// C'tor for Excel sheet importation as an SDMX artefact
        /// </summary>
        /// <param name="cfg">Mapping configuration</param>
        /// <param name="dtWorksheet">Datatable representation of Excel worksheet</param>
        public ImportArtefactExcel(BaseCfg cfg, DataTable dtWorksheet)
        {
            _cfg = cfg;
            _arCfg = _cfg.Artefact;
            _dtWorksheet = dtWorksheet;
            wh = new WorkSheetHelper(_dtWorksheet);
        }
        #endregion //Construction

        #region Public Methods

        /// <summary>Artefact properties retrieved from Excel Sheet </summary>
        /// <param name="a">the artefact to fill</param>
        public void FillArtefact(Artefact a)
        {
            // Something to do ?
            if (_arCfg.Row > 0)
            {
                a.Id = wh.GetCellContent(_arCfg.Row, _arCfg.ColID, a.Id);
                a.AgencyId = wh.GetCellContent(_arCfg.Row, _arCfg.ColAgencyID, a.AgencyId);
                a.Version = wh.GetVersion(_arCfg.Row, _arCfg.ColVersion, a.Version);
                a.Name = wh.GetMultilang(_arCfg.Row, _arCfg.NameColumns);
                a.Description = wh.GetMultilang(_arCfg.Row, _arCfg.DescriptionColumns);
                a.ValidFrom = wh.GetDateStr(_arCfg.Row, _arCfg.ColValidFrom);
                a.ValidTo = wh.GetDateStr(_arCfg.Row, _arCfg.ColValidTo);

                if (a.Name.IsEmpty)
                    a.Name.TextList.Add(new TextType(LangSupport.CurLang, "Text n. def."));
            }
        }

        /// <summary>
        /// Create artefact items collection with optional hierarchy
        /// </summary>
        /// <param name="a"></param>
        /// <param name="deduceHierarchy">True, hierarchy deduced from code width</param>
        /// <returns>Container with created artefact(s)</returns>
        public SmManager FillCodes(Artefact a, bool deduceHierarchy, bool cleanEmptyAnnot)
        {
            SmManager sm = null;
            cleanEmptyAnnotations = cleanEmptyAnnot;

            // Hierarchical Codelist:
            // Specific Mode: HierarchyParentAndLevel
            // Others:
            // - First: Create a codelist with flat hierarchy
            // - Then: Create the hierarchy and a codelist for each level
            // Finally: Place all in an SmManager.
            HierarchicalCodelistType hcl = a as HierarchicalCodelistType;
            if (hcl != null)
            {
                CodeListType cl = new CodeListType();
                a = cl;

                // Special MEB import according to parent and level info 
                if (_cfg.ColParentMode == ParentDefinitionMode.HierarchyParentAndLevel)
                {
                    sm = FillLevelSpecial(hcl);
                    return sm;
                }
                else if (_cfg.ColParentMode == ParentDefinitionMode.Level)
                {
                    sm = FillLevelForHierarchy(hcl);
                    return sm;
                }
            }

            // Do we have all info in one row
            if (_cfg.AllInOneRow)
            {
                AllInOneRow allone = new AllInOneRow(_cfg, _dtWorksheet);
                wh = allone.WsHelper;
            }

            if (deduceHierarchy)
                FillDeduceFlatHierarchy(a);
            else
                if (a is CategorySchemeType)
                    FillNested(a as CategorySchemeType);
                else
                    FillFlat(a);

            if (hcl != null)
            {
                CodelistToHCL hclConvert = new CodelistToHCL();
                sm = hclConvert.Transform(a as CodeListType, hcl);
            }

            return sm;
        }

        #endregion //Public Methods

        #region Internal Methods

        private void FillNested(CategorySchemeType cst)
        {
            int row = 0;
            //fhl.HasHierarchy = _cfg.ColParentMode != ParentDefinitionMode.None;
            List<Category> listLevels = new List<Category>();
            listLevels.Add(null);
            try
            {
                if (_dtWorksheet != null && cst != null)
                {
                    for (row = _cfg.HeaderRowCount + 1; row <= wh.RowCount; ++row)
                    {
                        // All lines or only indicated group.
                        if (_cfg.GroupCfg.IsUsed == false ||
                            (wh.GetCellContent(row, _cfg.GroupCfg.ColKey) == _cfg.GroupCfg.GroupKey))
                        {
                            Category c = new Category(cst.CategoryList, null);
                            string id = wh.GetCellContent(row, _cfg.ColKey).Trim();
                            if (id.Length == 0)
                                break;
                            c.Id = id;
                            MultiLang ml = wh.GetMultilang(row, _cfg.NameColumns);
                            c.Name.TextList = ml.TextList;

                            // Hierarchy
                            if (_cfg.ColParentKey > 0 && _cfg.ColParentMode == ParentDefinitionMode.Level)
                            {
                                string parentInfo = wh.GetCellContent(row, _cfg.ColParentKey);
                                if (_cfg.ColParentMode == ParentDefinitionMode.Level)
                                {
                                    int level = Convert.ToInt32(parentInfo);
                                    Category parentItem = listLevels[level - 1];
                                    NestedHierarchicalList nhl = parentItem != null ? parentItem.InnerHierarchy as NestedHierarchicalList : cst.CategoryList;
                                    if (listLevels.Count <= level)
                                        listLevels.Add(c);
                                    else
                                        listLevels[level] = c;

                                    nhl.Add(c);

                                }
                            }
                            else
                                cst.CategoryList.Add(c);

                            // All attributes of code object
                            FillAttrib(row, c);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error occured in line: " + row.ToString() + ". Reason: " + ex.Message);
            }
        }

        private void FillFlat(Artefact a)
        {
            if (_dtWorksheet != null && a != null)
            {
                FlatHierarchicalList fhl = GetFlatHierarchyList(a);
                fhl.HasHierarchy = _cfg.ColParentMode != ParentDefinitionMode.None;
                List<ISdmxHierarchyListItem> listLevels = new List<ISdmxHierarchyListItem>();
                listLevels.Add(null);


                for (int row = _cfg.HeaderRowCount + 1; row <= wh.RowCount; ++row)
                {
                    // All lines or only indicated group.
                    if (_cfg.GroupCfg.IsUsed == false ||
                        (wh.GetCellContent(row, _cfg.GroupCfg.ColKey) == _cfg.GroupCfg.GroupKey))
                    {
                        ISdmxHierarchyListItem c = CreateItem(a);
                        c.Id = wh.GetCellContent(row, _cfg.ColKey);
                        if (string.IsNullOrEmpty(c.Id.Trim()))
                            break;

                        MultiLang ml = wh.GetMultilang(row, _cfg.NameColumns);
                        c.Name.TextList = ml.TextList;

                        // Hierarchy
                        if (_cfg.ColParentKey > 0 && _cfg.ColParentMode != ParentDefinitionMode.None)
                        {
                            string parentInfo = wh.GetCellContent(row, _cfg.ColParentKey);
                            if (_cfg.ColParentMode == ParentDefinitionMode.Level)
                            {
                                int level = Convert.ToInt32(parentInfo);
                                ISdmxHierarchyListItem parentItem = listLevels[level - 1];
                                c.Parent = parentItem != null ? parentItem.Id : string.Empty;
                                if (listLevels.Count <= level)
                                    listLevels.Add(c);
                                else
                                    listLevels[level] = c;

                            }
                            else
                                c.Parent = parentInfo;
                        }

                        // TextType for Concepts
                        if (_cfg.ColTextFormat > 0)
                        {
                            ConceptType concept = c as ConceptType;
                            if (concept != null)
                            {
                                string textType = wh.GetCellContent(row, _cfg.ColTextFormat);
                                concept.TextFormat.TextType = TextFormatType.GetTypeFromString(textType);
                                if (concept.TextFormat.TextType != TextTypeType.Undefined)
                                {
                                    string textMaxLen = wh.GetCellContent(row, _cfg.ColTFormatMaxLen);
                                    concept.TextFormat.maxLength = textMaxLen;
                                    string textMinLen = wh.GetCellContent(row, _cfg.ColTFormatMinLen);
                                    concept.TextFormat.minLength = textMinLen;
                                    string decimalCnt = wh.GetCellContent(row, _cfg.ColTFormatDecimal);
                                    concept.TextFormat.decimals = decimalCnt;
                                }
                                if (concept.TextFormat.IsNumeric)
                                {
                                    concept.TextFormat.startValue = wh.GetCellContent(row, _cfg.ColTFormatMinVal);
                                    concept.TextFormat.endValue = wh.GetCellContent(row, _cfg.ColTFormatMaxVal);
                                }
                            }
                        }

                        // Codelist ref
                        if (_cfg.ColCodelistRef > 0)
                        {
                            string fullId = wh.GetCellContent(row, _cfg.ColCodelistRef);
                            string[] arr = fullId.Split('+');
                            if (arr.Length == 3)
                            {
                                (c as ConceptType).CoreRepresentation.AgencyId = arr[0];
                                (c as ConceptType).CoreRepresentation.Id = arr[1];
                                (c as ConceptType).CoreRepresentation.Version = arr[2];
                            }
                        }

                        // All attributes of code object
                        FillAttrib(row, c);

                        // Validity interval
                        ValidFromTo(c, row, wh);

                        // Add in container
                        fhl.Add(c);
                    }
                }
            }
        }

        private void ValidFromTo(ISdmxHierarchyListItem c, int row, WorkSheetHelper wh)
        {
            // Valid from and Valid to
            string vf = wh.GetDateStr(row, _cfg.ColValidFrom);
            string vt = wh.GetDateStr(row, _cfg.ColValidTo);
            if (vf.Length > 0)
            {
                DateTime dt;
                if (DateTime.TryParse(vf, out dt))
                {
                    Annotation av = new Annotation(TYPE_VALID_FROM);
                    av.Title = dt.ToString(DATE_FORMAT);
                    c.AnnotationList.Add(av);
                }
            }
            if (vt.Length > 0)
            {
                DateTime dt;
                if (DateTime.TryParse(vt, out dt))
                {
                    Annotation av = new Annotation(TYPE_VALID_TO);
                    av.Title = dt.ToString(DATE_FORMAT);
                    c.AnnotationList.Add(av);
                }
            }
        }

        /// <summary>
        /// Flat hierarchy (codes and concepts) is deduced from codes length
        /// </summary>
        /// <param name="a">The parent artefact</param>
        /// <returns></returns>
        private FlatHierarchicalList GetFlatHierarchyList(Artefact a)
        {
            FlatHierarchicalList fhl = null;
            if (a is CodeListType)
                fhl = (a as CodeListType).CodeList;

            else if (a is ConceptSchemeType)
                fhl = (a as ConceptSchemeType).ConceptList;

            return fhl;
        }

        /// <summary> Nested hierarchy (Categories) </summary>
        /// <param name="a">The parent artefact</param>
        /// <returns></returns>
        private NestedHierarchicalList GetNestedHierarchyList(Artefact a)
        {
            NestedHierarchicalList nhl = null;
            if (a is CategorySchemeType)
                nhl = (a as CategorySchemeType).CategoryList;

            return nhl;
        }
        private ISdmxHierarchyListItem CreateItem(Artefact a)
        {
            ISdmxHierarchyListItem it = null;
            if (a is CodeListType)
                it = new CodeType();

            else if (a is ConceptSchemeType)
                it = new ConceptType();

            /*
            else if (a is CategorySchemeType)
                it = new Category((a as CategorySchemeType).CategoryList);
*/
            return it;
        }

        /// <summary>
        /// Flat hierarchy (codes and concepts) is deduced from codes length
        /// </summary>
        /// <param name="a">The parent artefact</param>
        private TreeNode FillDeduceFlatHierarchy(Artefact a)
        {
            // Assume a max depth of 20
            List<int> listLevelSize = new List<int>(20);
            listLevelSize.Add(0);
            int level = 0;
            TreeNode rootNode = new TreeNode();
            TreeNode curParent = rootNode;
            int line = 0;

            if (_dtWorksheet != null && a != null)
            {
                for (int row = _cfg.HeaderRowCount + 1; row <= wh.RowCount; ++row)
                {
                    // All lines or only indicated group.
                    if (_cfg.GroupCfg.IsUsed == false ||
                        (wh.GetCellContent(row, _cfg.GroupCfg.ColKey) == _cfg.GroupCfg.GroupKey))
                    {

                        ISdmxHierarchyListItem c = CreateItem(a);
                        c.Id = wh.GetCellContent(row, _cfg.ColKey);
                        MultiLang ml = wh.GetMultilang(row, _cfg.NameColumns);
                        c.Name.TextList = ml.TextList;

                        // All attributes of code object
                        FillAttrib(row, c);

                        ++line;
                        level = Updatelevel(listLevelSize, c.Id);

                        // retrieve parent node
                        curParent = UpdateCurParentNode(rootNode, level, c);

                    }
                }
                FlatHierarchicalList fhl = GetFlatHierarchyList(a);
                if (fhl != null)
                    NodesToList(fhl, rootNode); // level0 is root, so we skip it    
                else
                {
                    NestedHierarchicalList nhl = GetNestedHierarchyList(a);
                    if (nhl != null)
                        NodesToNested(nhl, rootNode);
                }
            }
            return rootNode;
        }

        private int Updatelevel(List<int> listLevelSize, string itemId)
        {
            int level = 0;
            for (int i = 0; i < listLevelSize.Count; i++)
                if (listLevelSize[i] == itemId.Length ||
                    (i < listLevelSize.Count - 1 && itemId.Length > listLevelSize[i] &&
                    itemId.Length < listLevelSize[i + 1]))
                {
                    level = i;
                    break;
                }

            if (level == 0)
            {
                level = listLevelSize.Count;
                listLevelSize.Add(itemId.Length);
            }

            return level;
        }

        private TreeNode UpdateCurParentNode(TreeNode rootNode, int level, ISdmxHierarchyListItem item)
        {
            // retrieve parent node
            TreeNode curParent = rootNode;
            while (curParent.Level + 1 < level)
                curParent = curParent.LastNode;

            TreeNode nd = new TreeNode(item.ToString());
            nd.Tag = item;
            curParent.Nodes.Add(nd);

            return curParent;
        }

        // From Tree hierarchy fill codelist recursively
        private void NodesToList(FlatHierarchicalList fhl, TreeNode node)
        {
            if (fhl != null)
            {
                fhl.HasHierarchy = true;
                foreach (TreeNode nd in node.Nodes)
                {
                    ISdmxHierarchyListItem c = nd.Tag as ISdmxHierarchyListItem;
                    ISdmxHierarchyListItem parent = node.Tag as ISdmxHierarchyListItem;
                    if (parent != null)
                        c.Parent = parent.Id;
                    fhl.Add(c);
                    NodesToList(fhl, nd);
                }
            }
        }
        // From Tree hierarchy fill codelist recursively
        private void NodesToNested(NestedHierarchicalList nhl, TreeNode node)
        {
            if (nhl != null)
            {
                foreach (TreeNode nd in node.Nodes)
                {
                    Category c = nd.Tag as Category;
                    Category parent = node.Tag as Category;
                    if (parent != null)
                        nhl = parent.CategoryList;
                    nhl.Add(c); ;
                    NodesToNested(nhl, nd);
                }
            }
        }

        private void FillAttrib(int row, ISdmxHierarchyListItem item)
        {
            // official Title is always present
            MultiLang ml = wh.GetMultilang(row, _cfg.NameColumns);
            item.Name.TextList = ml.TextList; // read only Name

            // Annotation i
            Annotation an = null;
            if (string.IsNullOrEmpty(_cfg.AnnotationType) == false)
            {
                an = new Annotation(_cfg.AnnotationType);
                ml = wh.GetMultilang(row, _cfg.AnnotationColumns);
                if (ml.IsEmpty == false)
                    an.Text = ml;
                string title = wh.GetCellContent(row, _cfg.ColAnnotationTitle);
                if (string.IsNullOrEmpty(title) == false)
                    an.Title = title;

                string s = wh.GetCellContent(row, _cfg.ColAnnotationUrn);
                if (string.IsNullOrEmpty(s) == false)
                {
                    try
                    {
                        an.Url = new Uri(s);
                    }
                    catch { }
                }
                if (an != null)
                {
                    // Fill if requested
                    if (cleanEmptyAnnotations == false || an.Text.IsEmpty == false || (an.Title != null && an.Title.Length > 0) || an.Url != null)
                        item.AnnotationList.Add(an);
                }
            }

            // Description 
            ml = wh.GetMultilang(row, _cfg.DescriptionColumns);
            if (item is ConceptType)
                (item as ConceptType).Description = ml;
            else if (item is Category)
                (item as Category).Description = ml;
            else if (item is CodeType)
                (item as CodeType).Description = ml;
        }

        #endregion // Internal Methods

        #region Hierarchy Level special Attach

        /// <summary>
        /// Special MEB Requirement, level depth info can be higher than hierarchy tree depth
        /// </summary>
        /// <param name="hcl">The hierarchical codelist where to construct hierarchy</param>
        /// <returns>the container holding hierarchical codelist and codelists associated</returns>
        private SmManager FillLevelSpecial(HierarchicalCodelistType hcl)
        {
            SmManager sm = new SmManager();
            sm.PutArtefact(SdmxArtefactType.HierarchicalCodelists, hcl);


            if (_cfg.ColParentKey <= 0 || _cfg.ColParentMode == ParentDefinitionMode.None)
                throw new ApplicationException("Unexpected no parent specification given");

            if (_dtWorksheet != null && hcl != null)
            {
                // codelist for each level indicated in import
                Dictionary<string, CodeListType> dicoCodelist = new Dictionary<string, CodeListType>();
                string levelBaseId = "LV_";
                // Create Hierarchy container
                HierarchyType heType = new HierarchyType(hcl.CodelistRefList);
                heType.Id = "HR_" + hcl.Id.Substring(4);
                heType.Name.UpdateDefaultText("Hierarchy");
                hcl.HierarchyList.Clear();
                hcl.HierarchyList.Add(heType);

                string curParentInfo = string.Empty;
                CodeRefTypeList CurRefTypeList = heType.CodeRefList;

                for (int row = _cfg.HeaderRowCount + 1; row <= wh.RowCount; ++row)
                {
                    // All lines or only indicated group.
                    if (_cfg.GroupCfg.IsUsed == false ||
                        (wh.GetCellContent(row, _cfg.GroupCfg.ColKey) == _cfg.GroupCfg.GroupKey))
                    {
                        CodeType c = new CodeType();
                        c.Id = wh.GetCellContent(row, _cfg.ColKey);

                        // Stop if no code given in current row
                        if (string.IsNullOrEmpty(c.Id))
                            break;

                        MultiLang ml = wh.GetMultilang(row, _cfg.NameColumns);
                        c.Name.TextList = ml.TextList;

                        // Hierarchy, the two info are required
                        string parentInfo = wh.GetCellContent(row, _cfg.ColParentKey);
                        string levelInfo = wh.GetCellContent(row, _cfg.ColParentKey + 1);

                        // check level validity
                        int level;
                        if (int.TryParse(levelInfo, out level) == false)
                            throw new ApplicationException("Invalid level info: " + levelInfo);
                        if (level < 1 || level > 100)
                            throw new ApplicationException("Invalid level info: " + levelInfo);

                        // Create hierarchy info
                        CodeRefType crt = new CodeRefType();
                        crt.CodeID = c.Id;
                        crt.LevelRef = levelBaseId + levelInfo;
                        crt.CodelistAliasRef = levelInfo;

                        // Valid from and Valid to
                        string vf = wh.GetDateStr(row, _cfg.ColValidFrom);
                        string vt = wh.GetDateStr(row, _cfg.ColValidTo);
                        if (vf.Length > 0)
                        {
                            DateTime dt;
                            if (DateTime.TryParse(vf, out dt))
                            {
                                crt.ValidFrom = dt.ToString(DATE_FORMAT);
                            }
                        }
                        if (vt.Length > 0)
                        {
                            DateTime dt;
                            if (DateTime.TryParse(vt, out dt))
                            {
                                crt.ValidTo = dt.ToString(DATE_FORMAT);
                            }
                        }

                        // Locate parent for hierarchy construction
                        if (parentInfo == string.Empty) // root
                        {
                            heType.CodeRefList.Add(crt);
                            curParentInfo = string.Empty;
                            CurRefTypeList = heType.CodeRefList;
                        }
                        else if (curParentInfo == parentInfo) // current container
                        {
                            CurRefTypeList.Add(crt);
                        }
                        else
                        {
                            // Locate parent
                            CurRefTypeList = heType.CodeRefList.GetCodeRefListOfCode(parentInfo);
                            if (CurRefTypeList == null)
                                throw new ApplicationException("Not found: Parent info of " + parentInfo);
                            foreach (CodeRefType crtInParent in CurRefTypeList)
                            {
                                if (crtInParent.CodeID == parentInfo)
                                {
                                    CurRefTypeList = crtInParent.CodeRefList;
                                    break;
                                }
                            }

                            CurRefTypeList.Add(crt);
                            curParentInfo = parentInfo;
                        }

                        // All attributes of code object
                        FillAttrib(row, c);

                        // Add in container
                        if (dicoCodelist.ContainsKey(levelInfo) == false)
                        {
                            CodeListType clt = new CodeListType();
                            clt.Id = "CL_LEVEL_" + levelInfo;
                            clt.AgencyId = hcl.AgencyId;
                            clt.Name.UpdateDefaultText("codelist level " + levelInfo + " for  " + hcl.Id);
                            dicoCodelist.Add(levelInfo, clt);
                            sm.PutArtefact(SdmxArtefactType.CodeLists, clt);
                        }

                        // Add code in codelist if not already present
                        if (dicoCodelist[levelInfo].CodeList.LocateItem(c.Id) == null)
                            dicoCodelist[levelInfo].CodeList.Add(c);
                    }
                }

                // Create references
                List<string> list = new List<string>(dicoCodelist.Keys);
                list.Sort();

                // Loop through keys.
                int order = 1;
                foreach (string key in list)
                {
                    CodeListType cltLevel = dicoCodelist[key];
                    CodelistRefType che = new CodelistRefType();
                    che.AgencyId = cltLevel.AgencyId;
                    che.Id = cltLevel.Id;
                    che.Alias = key;
                    che.Version = cltLevel.Version;
                    heType.CodelistRefList.Add(che);

                    LevelType level = new LevelType();
                    level.Id = levelBaseId + che.Alias;
                    foreach (string lg in LangSupport.SupportedLanguages)
                        level.Name.UpdateDefaultText(" level " + key);

                    // level.CodingType.TextType = TextTypeType.Integer;
                    level.Order = order++;
                    heType.LevelList.Add(level);
                }

            }
            return sm;
        }


        #endregion

        CodeRefTypeList GetRefTypeList(HierarchyType heType, int level)
        {
            CodeRefTypeList curRefTypeList = heType.CodeRefList;

            if (level > 1)
            {
                int lvl = 1;
                while (lvl++ < level)
                {
                    curRefTypeList = curRefTypeList[curRefTypeList.Count - 1].CodeRefList;
                }
            }
            return curRefTypeList;
        }


        /// <summary>
        /// Special MEB Requirement, level depth info can be higher than hierarchy tree depth
        /// </summary>
        /// <param name="hcl">The hierarchical codelist where to construct hierarchy</param>
        /// <returns>the container holding hierarchical codelist and codelists associated</returns>
        private SmManager FillLevelForHierarchy(HierarchicalCodelistType hcl)
        {
            SmManager sm = new SmManager();
            sm.PutArtefact(SdmxArtefactType.HierarchicalCodelists, hcl);


            if (_cfg.ColParentKey <= 0 || _cfg.ColParentMode == ParentDefinitionMode.None)
                throw new ApplicationException("Unexpected no parent specification given");

            if (_dtWorksheet != null && hcl != null)
            {
                // codelist for each level indicated in import
                Dictionary<string, CodeListType> dicoCodelist = new Dictionary<string, CodeListType>();
                string levelBaseId = "LV_";
                // Create Hierarchy container
                HierarchyType heType = new HierarchyType(hcl.CodelistRefList);
                heType.Id = "HR_" + hcl.Id.Substring(4);
                heType.Name.UpdateDefaultText("Hierarchy");
                hcl.HierarchyList.Clear();
                hcl.HierarchyList.Add(heType);

                string curParentInfo = string.Empty;
                CodeRefTypeList CurRefTypeList = heType.CodeRefList;

                for (int row = _cfg.HeaderRowCount + 1; row <= wh.RowCount; ++row)
                {
                    try
                    {

                        // All lines or only indicated group.
                        if (_cfg.GroupCfg.IsUsed == false ||
                            (wh.GetCellContent(row, _cfg.GroupCfg.ColKey) == _cfg.GroupCfg.GroupKey))
                        {
                            CodeType c = new CodeType();
                            c.Id = wh.GetCellContent(row, _cfg.ColKey);

                            // Stop if no code given in current row
                            if (string.IsNullOrEmpty(c.Id))
                                break;

                            MultiLang ml = wh.GetMultilang(row, _cfg.NameColumns);
                            c.Name.TextList = ml.TextList;

                            // Hierarchy, the two info are required
                            string levelInfo = wh.GetCellContent(row, _cfg.ColParentKey + 1);

                            // check level validity
                            int level;
                            if (int.TryParse(levelInfo, out level) == false)
                                throw new ApplicationException("Invalid level info: " + levelInfo);
                            if (level < 1 || level > 100)
                                throw new ApplicationException("Invalid level info: " + levelInfo);

                            // Create hierarchy info
                            CodeRefType crt = new CodeRefType();
                            crt.CodeID = c.Id;
                            crt.CodelistAliasRef = levelInfo;

                            // Valid from and Valid to
                            string vf = wh.GetDateStr(row, _cfg.ColValidFrom);
                            string vt = wh.GetDateStr(row, _cfg.ColValidTo);
                            if (vf.Length > 0)
                            {
                                DateTime dt;
                                if (DateTime.TryParse(vf, out dt))
                                {
                                    crt.ValidFrom = dt.ToString(DATE_FORMAT);
                                }
                            }
                            if (vt.Length > 0)
                            {
                                DateTime dt;
                                if (DateTime.TryParse(vt, out dt))
                                {
                                    crt.ValidTo = dt.ToString(DATE_FORMAT);
                                }
                            }

                            CurRefTypeList = GetRefTypeList(heType, level);
                            CurRefTypeList.Add(crt);

                            // All attributes of code object
                            FillAttrib(row, c);

                            // Add in container
                            if (dicoCodelist.ContainsKey(levelInfo) == false)
                            {
                                CodeListType clt = new CodeListType();
                                clt.Id = "CL_LEVEL_" + levelInfo;
                                clt.AgencyId = hcl.AgencyId;
                                clt.Name.UpdateDefaultText("codelist level " + levelInfo + " for  " + hcl.Id);
                                dicoCodelist.Add(levelInfo, clt);
                                sm.PutArtefact(SdmxArtefactType.CodeLists, clt);
                            }

                            // Add code in codelist if not already present
                            if (dicoCodelist[levelInfo].CodeList.LocateItem(c.Id) == null)
                                dicoCodelist[levelInfo].CodeList.Add(c);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new ApplicationException("Error in row " + row.ToString(), ex);
                    }
                }


                // Create references
                List<string> list = new List<string>(dicoCodelist.Keys);
                list.Sort();

                // Loop through keys.
                int order = 1;
                foreach (string key in list)
                {
                    CodeListType cltLevel = dicoCodelist[key];
                    CodelistRefType che = new CodelistRefType();
                    che.AgencyId = cltLevel.AgencyId;
                    che.Id = cltLevel.Id;
                    che.Alias = key;
                    che.Version = cltLevel.Version;
                    heType.CodelistRefList.Add(che);

                    LevelType level = new LevelType();
                    level.Id = levelBaseId + che.Alias;
                    foreach (string lg in LangSupport.SupportedLanguages)
                        level.Name.UpdateDefaultText(" level " + key);

                    // level.CodingType.TextType = TextTypeType.Integer;
                    level.Order = order++;
                    heType.LevelList.Add(level);
                }
            }


            return sm;
        }
    }


}
